package com.beiming.libra.common.utils;

import com.beiming.libra.common.constants.ExcelConstants;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * @author tp Date : 2018/6/14/014 15:11
 */
public class ExcelReaderUtil {

  // excel2003扩展名
  public static final String EXCEL03_EXTENSION = ".xls";
  // excel2007扩展名
  public static final String EXCEL07_EXTENSION = ".xlsx";

  private static final Logger LOGGER = LoggerFactory.getLogger(ExcelReaderUtil.class);

  /**
   * 读取Excel文件，可能是03也可能是07版本
   */
  public static List<List<Object>> readExcel(String fileName) throws Exception {
    // 处理excel2003文件
    List<List<Object>> excelBeanList = null;
    if (fileName.endsWith(EXCEL03_EXTENSION)) {
      ExcelXlsReader exceXls = new ExcelXlsReader();
      excelBeanList = exceXls.excelReader(fileName);
      // 处理excel2007文件
    } else if (fileName.endsWith(EXCEL07_EXTENSION)) {
      ExcelXlsxReader exceXlsx = new ExcelXlsxReader();
      excelBeanList = exceXlsx.excelReader(fileName);
    } else {
      throw new Exception("文件格式错误，fileName的扩展名只能是xls或xlsx。");
    }
    return excelBeanList;
  }

  /**
   * 对excel中数据进行校验
   */
  public static Map<String, Object> createExcelBean(List<List<Object>> allValueList) {
    // 获得标题
    Map<String, Object> dicMap = new HashMap<>();
    List<Object> objects = allValueList.get(0);
    allValueList.remove(0);
    List<ExcelBean> excelBeanList = new ArrayList<>();
    List<ErrorBean> errorBeanList = new ArrayList<>();
    allValueList.forEach(list -> {
      Class<ExcelBean> excelBeanClass = ExcelBean.class;
      try {
        ExcelBean excelBean = excelBeanClass.newInstance();
        for (int i = 0; i < list.size(); i++) {
          String title = objects.get(i).toString().trim();
          if (ExcelConstants.constantMap.get(title) != null) {
            String titleField = ExcelConstants.constantMap.get(title).toString();
            validateParam(errorBeanList, list.get(i) + "", titleField, title);
            Field declaredField = excelBeanClass.getDeclaredField(titleField);
            declaredField.setAccessible(true);
            declaredField.set(excelBean, list.get(i));
          } else {
            if (dicMap.get(title) == null) {
              ErrorBean errorBean = new ErrorBean();
              errorBean.setErrorName(ExcelConstants.TEMPLATE_QUESTION);
              errorBean.setErrorFile(ExcelConstants.CASEXLS);
              errorBean.setErrorReason(
                  String.format(ExcelConstants.VERIFY_TEMPLATE, objects.get(i).toString()));
              errorBeanList.add(errorBean);
              dicMap.put(title, title);
            }
          }
        }
        excelBeanList.add(excelBean);
      } catch (Exception e) {
        e.printStackTrace();
      }
    });
    List<ErrorBean> sortedList = errorBeanList.stream()
        .sorted((e1, e2) -> e1.getErrorName().length() - e2.getErrorName().length())
        .collect(Collectors.toList());
    Map<String, Object> resultMap = new HashMap<>();
    resultMap.put("excelBean", excelBeanList);
    resultMap.put("errorList", sortedList);
    return resultMap;
  }

  /**
   * 解析excel绑定参数
   */
  public static Map<String, Object> parseExcel(String fileName) {
    try {
      List<List<Object>> lists = readExcel(fileName);
      return createExcelBean(lists);
    } catch (Exception e) {
      e.printStackTrace();
      LOGGER.error("解析excel出错{}", e.getMessage());
    }
    return null;
  }

  /**
   * 验证excel中的数据
   */
  private static void validateParam(List<ErrorBean> resultList, String stringVal,
      String filedName, String title) {
    ErrorBean errorBean = new ErrorBean();
    if (StringUtils.isEmpty(stringVal)) {
      errorBean.setErrorName(ExcelConstants.VERIFY_QUESTION);
      errorBean.setErrorFile(ExcelConstants.CASEXLS);
      errorBean.setErrorReason(
          String.format(ExcelConstants.VERIFY_EMPTY, title));
      resultList.add(errorBean);
      return;
    }
    errorBean.setErrorName(ExcelConstants.VERIFY_QUESTION);
    errorBean.setErrorFile(ExcelConstants.CASEXLS);
    if (ExcelConstants.CODE.equals(filedName)) {
      if (!ValidateExcelData.isIDCard(stringVal)) {
        errorBean.setErrorReason(
            String.format(ExcelConstants.VERIFY_ERROR, title));
      }
    } else if (ExcelConstants.MOBILEPHONE.equals(filedName)) {
      if (!ValidateExcelData.isMobile(stringVal)) {
        errorBean.setErrorReason(
            String.format(ExcelConstants.VERIFY_ERROR, title));
      }
    } else if (ExcelConstants.MAILADDRESS.equals(filedName)) {
      if (!ValidateExcelData.isEmail(stringVal)) {
        errorBean.setErrorReason(
            String.format(ExcelConstants.VERIFY_ERROR, title));
      }
    } else if (ExcelConstants.PRINCIPAL.equals(filedName)) {
      if (!ValidateExcelData.isNumber(stringVal)) {
        errorBean.setErrorReason(
            String.format(ExcelConstants.VERIFY_ERROR, title));
      }
    } else if (ExcelConstants.FEE.equals(filedName)) {
      if (!ValidateExcelData.isNumber(stringVal)) {
        errorBean.setErrorReason(
            String.format(ExcelConstants.VERIFY_ERROR, title));
      }
    } else if (ExcelConstants.INTERESTS.equals(filedName)) {
      if (!ValidateExcelData.isNumber(stringVal)) {
        errorBean.setErrorReason(
            String.format(ExcelConstants.VERIFY_ERROR, title));
      }
    } else if (ExcelConstants.CARDNO.equals(filedName)) {
      if (!ValidateExcelData.isCardNo(stringVal)) {
        errorBean.setErrorReason(
            String.format(ExcelConstants.VERIFY_ERROR, title));
      }
    } else if (ExcelConstants.ACCOUNTNO.equals(filedName)) {
      if (!ValidateExcelData.isCardNo(stringVal)) {
        errorBean.setErrorReason(
            String.format(ExcelConstants.VERIFY_ERROR, title));
      }
    }
    if (StringUtils.isNotEmpty(errorBean.getErrorReason())) {
      resultList.add(errorBean);
    }
  }
}
